

def create_tables():
    import sqlite3
    # 创建数据库连接
    conn = sqlite3.connect('tables.db')

    # 创建游标
    cursor = conn.cursor()


    # 创建班级表
    cursor.execute('''CREATE TABLE IF NOT EXISTS class (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        class_name TEXT NOT NULL,
                        teacher_id INTEGER, 
                        job_id TEXT NOT NULL,
                        student TEXT,
                        paper TEXT 
                        
                    )''')
    # 创建学生表
    cursor.execute('''CREATE TABLE IF NOT EXISTS students (
                         student_id INTEGER PRIMARY KEY AUTOINCREMENT,
                         number TEXT,
                         name TEXT,
                         clsaa TEXT,
                         score TEXT,
                         username TEXT,
                         password TEXT,
                         security_question TEXT,
                         gender TEXT
                     )''')
    # 创建关联表student_classes
    cursor.execute('''CREATE TABLE IF NOT EXISTS student_classes (  
                        id INTEGER PRIMARY KEY AUTOINCREMENT,  
                        student_id INTEGER,  
                        class_id INTEGER,  
                        FOREIGN KEY (student_id) REFERENCES students(student_id),  
                        FOREIGN KEY (class_id) REFERENCES class(id)  
                    )''')

    # 创建老师表
    cursor.execute('''CREATE TABLE IF NOT EXISTS teachers (
                        job_id INTEGER PRIMARY KEY AUTOINCREMENT,
                        number TEXT,
                        name TEXT,  
                        class_all TEXT,     
                        username TEXT,
                        password TEXT,
                        security_question TEXT,
                        gender TEXT,
                        class_id INTEGER
                    )''')

    # 创建一个题库表格
    cursor.execute('''CREATE TABLE IF NOT EXISTS questions  
                (id INTEGER PRIMARY KEY AUTOINCREMENT,  
                 question TEXT NOT NULL,  
                 answer TEXT NOT NULL)''')

    # 创建一个试卷表格
    cursor.execute('''CREATE TABLE IF NOT EXISTS papers  
                (id INTEGER PRIMARY KEY AUTOINCREMENT,  
                 title TEXT NOT NULL,  
                 class_id INTEGER,  
                 FOREIGN KEY (class_id) REFERENCES classes(id))''')

    # 创建一个试卷题目关联表格
    cursor.execute('''CREATE TABLE IF NOT EXISTS paper_questions  
                (id INTEGER PRIMARY KEY AUTOINCREMENT,  
                 paper_id INTEGER,  
                 question_id INTEGER,  
                 FOREIGN KEY (paper_id) REFERENCES papers(id),  
                 FOREIGN KEY (question_id) REFERENCES questions(id))''')


    # 提交更改
    conn.commit()

    # 关闭游标和连接
    cursor.close()
    conn.close()


if __name__ == '__main__':

    try:
        create_tables()
    except rospy.ROSInterruptException:
    	pass